using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

 
    public class DBUtilSQLServer
    {
       
    
        public static string executeQuery(string spName, ArrayList alParametros, int parametro)
        {

            string valor = "";

            SqlConnection conn = getConnection( );
            SqlCommand cmd = getCommand(conn, spName, alParametros);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            valor = cmd.Parameters[parametro].Value + "";

            closeConnection(conn);

            return valor;
        }

        public static void executeNonQuery(string spName, ArrayList alParametros)
        {
            SqlConnection conn = getConnection( );
            SqlCommand cmd = getCommand(conn, spName, alParametros);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            closeConnection(conn);
        }

        public static DataSet getDataset(string spName, ArrayList alParametros)
        {

            SqlConnection conn = getConnection();
            SqlCommand cmd = getCommand(conn, spName, alParametros);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            closeConnection(conn);
            return ds;
        }

        private static SqlConnection getConnection( )
        {

            SqlConnection conn = new SqlConnection(DALBase.prCadConexion);
            conn.Open();
            return conn;
        }

        private static SqlCommand getCommand(SqlConnection conn, string spName, ArrayList alParametros)
        {
            SqlCommand cmd = new SqlCommand(spName, conn);

            IEnumerator ieParametros = alParametros.GetEnumerator();
            while (ieParametros.MoveNext())
            {
                cmd.Parameters.Add((SqlParameter)ieParametros.Current);
            }

            return cmd;
        }

        private static SqlCommand getCommand(SqlConnection conn, string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);

            return cmd;
        }

        private static void closeConnection(SqlConnection conn)
        {
            conn.Close();
        }

        public static Hashtable getConnectionStringTable()
        {
            string[] parameters = DALBase.prCadConexion.Split(';');
            string[] key_value;
            Hashtable table = new Hashtable(4);

            foreach (string parameter in parameters)
            {
                key_value = parameter.Trim().Split('=');
                table.Add(key_value[0].Trim().ToUpper(), key_value[1]);
            }

            return table;
        }

        //public static DataSet GetDataSet(string spName, params SqlParameter[] commandParameters)
        //{
        //    ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings["csTempTraddo"];
        //    string connectionString = ConnectionStringSettings.ConnectionString;

        //    DataSet dst = null;
        //    dst = SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        //    return dst;
        //}

        //public static void ExecuteNonQuery(string spName, params SqlParameter[] commandParameters)
        //{
        //    ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings["csTempTraddo"];
        //    string connectionString = ConnectionStringSettings.ConnectionString;
        //    SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        //}
    }
 